package DataLayer.DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DataLayer.Model.DelOrdre;
import DataLayer.Model.Kunde;
import DataLayer.Model.Ordre;
import DataLayer.Model.Ordre.OrdreStatus;

public class DBOrdre implements IFDBOrdre
{
	private Connection con = DBConnection.getInstance().getDBcon();
	private ArrayList<Kunde> kdList;

	@Override
	public ArrayList<Ordre> getAllOrdre()
	{
		// This method is implemented for later use and is unused for now
		return miscWhere("", true);
	}

	public ArrayList<Ordre> getAllOrdreInfo()
	{
		return miscWhere("", false);
	}

	@Override
	public ArrayList<Ordre> getAllOrdreViaKundeNavn(String navn)
	{
		DBKunde dbKD = new DBKunde();
		int kundeId = dbKD.getKundeViaName(navn).getKundeId();
		return miscWhere("kundeId_fk = '" + kundeId + "'", true);
	}

	@Override
	public ArrayList<Ordre> getAllOrdreViaKundeEmail(String email)
	{
		DBKunde dbKD = new DBKunde();
		int kundeId = dbKD.getKundeViaEmail(email).getKundeId();
		return miscWhere("kundeId_fk = '" + kundeId + "'", true);
	}

	@Override
	public ArrayList<Ordre> getAllOrdreViaStatus(String status)
	{
		return miscWhere("ordreStatus = '" + status + "'", true);
	}

	@Override
	public ArrayList<Ordre> getAllOrdreViaoprettelsesDato(String oprettelsesDato)
	{
		return miscWhere("oprettelsesDato = '" + oprettelsesDato + "'", true);
	}

	@Override
	public Ordre getOrdre(int ordreId)
	{
		return singleWhere("ordreId = '" + ordreId + "'", true);
	}

	public Ordre getOrdreInfo(int ordreId)
	{
		return singleWhere("ordreId = '" + ordreId + "'", false);
	}

	@Override
	public int insertOrdre(Ordre o) throws Exception
	{
		int nextID = GetMax.getMaxId("SELECT MAX(ordreId) FROM Ordre");
		nextID = nextID + 1;
		int rc = -1;
		String query = "INSERT INTO Ordre(ordreId, oprettelsesDato, "
				+
				"leveringsDato, ordreStatus, ordreType, directOnly, rabat, total, debit, note, kundeId_fk)  VALUES('"
				+
				nextID + "','" +
				o.getOprettelsesDato() + "','" +
				o.getLeveringsDato() + "','" +
				o.getOrdreStatus() + "','" +
				o.getOrdreType() + "','" +
				(o.isDirectOnly() ? 1 : 0) + "','" +
				o.getRabat() + "','" +
				o.getTotal() + "','" +
				o.getDebit() + "','" +
				o.getNote() + "','" +
				o.getKunde().getKundeId() + "')";
		if (o.getDelOrdre().isEmpty())
		{
			throw new IllegalStateException("Empty array of DelOrdre");
		}
		System.out.println("insert : " + query);
		try
		{ // insert new Order
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex)
		{
			System.out.println("Ordre was not inserted");
		}
		for (DelOrdre delo : o.getDelOrdre())
		{
			DBDelOrdre dbDELO = new DBDelOrdre();
			dbDELO.insertDelOrdre(delo, nextID, o.getOrdreType());
		}
		return (rc);
	}

	@Override
	public int updateOrdre(Ordre o)
	{
		int rc = -1;
		String query = "UPDATE Ordre SET " +
				"oprettelsesDato ='" + o.getOprettelsesDato() + "'," +
				"leveringsDato ='" + o.getLeveringsDato() + "'," +
				"ordreStatus ='" + o.getOrdreStatus() + "'," +
				"directOnly ='" + (o.isDirectOnly() ? 1 : 0) + "'," +
				"rabat ='" + o.getRabat() + "'," +
				"total ='" + o.getTotal() + "'," +
				"debit ='" + o.getDebit() + "'," +
				"note ='" + o.getNote() + "'," +
				"kundeId_fk ='" + o.getKunde().getKundeId() + "'" +
				" WHERE ordreId = '" + o.getOrdreId() + "'";
		if (o.getDelOrdre().isEmpty())
		{
			throw new IllegalStateException("Empty array of DelOrdre");
		}
		for (DelOrdre delo : o.getDelOrdre())
		{
			DBDelOrdre dbDELO = new DBDelOrdre();
			if (delo.getDelordreId() < 0)
			{
				// To handle non added part orders, we try inserting them.
				try
				{
					dbDELO.insertDelOrdre(delo, o.getOrdreId(), o.getOrdreType());
				}
				catch (Exception e)
				{
					System.out.println("oprettelse af delordre fejlede");
				}
			}
			else if (delo.getDelordreId() > 0)
			{
				dbDELO.updateDelOrdre(delo, o.getOrdreId(), o.getOrdreType());
			}
		}
		System.out.println("Update query:" + query);
		try
		{ // update Order
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex)
		{
			System.out.println("Update exception in Ordre db: " + ex);
		}
		return (rc);
	}

	@Override
	public int deleteOrdre(Ordre o)
	{
		int rc = -1;
		String query = "DELETE FROM Ordre WHERE ordreId = '" + o.getOrdreId()
				+ "'";
		System.out.println(query);
		try
		{ // delete FROM Ordre
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex)
		{
			System.out.println("Delete exception in Ordre db: " + ex);
		}
		return (rc);
	}

	private ArrayList<Ordre> miscWhere(String wClause, boolean getDetails)
	{
		ResultSet results;
		ArrayList<Ordre> list = new ArrayList<Ordre>();
		String query = buildQuery(wClause);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Ordre o = new Ordre();
				o = buildMultipleOrdrers(results, getDetails);
				list.add(o);
			}
			stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Ordre singleWhere(String wClause, boolean getDetails)
	{
		ResultSet results;
		Ordre o = new Ordre();
		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next())
			{
				o = buildOrdre(results, getDetails);
				stmt.close();
			}
			else
			{
				o = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return o;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT ordreId, leveringsDato, oprettelsesDato, note, ordreType, directOnly, total, rabat, debit, ordreStatus, kundeId_fk FROM Ordre ";
		if (wClause.length() > 0)
		{
			query += "WHERE " + wClause;
		}
		return query;
	}

	/**
	 * builds an object o of the class Ordre
	 * 
	 * @param getDetails
	 * 
	 * @return Ordre
	 */
	private Ordre buildOrdre(ResultSet results, boolean getDetails)
	{
		Ordre o = new Ordre();
		try
		{
			o.setOrdreId(results.getInt("ordreId"));
			o.setLeveringsDato(results.getLong("leveringsDato"));
			o.setOprettelsesDato(results.getLong("oprettelsesDato"));
			o.setNote(results.getString("note"));
			o.setOrdreType(results.getString("ordreType"));
			o.setDirectOnly(results.getBoolean("directOnly"));
			o.setTotal(results.getDouble("total"));
			o.setRabat(results.getDouble("rabat"));
			o.setDebit(results.getDouble("debit"));
			for (OrdreStatus s : OrdreStatus.values())
			{
				if (s.toString().equals(results.getString("ordreStatus")))
				{
					o.setOrdreStatus(s);
				}
			}
			DBKunde dbKD = new DBKunde();
			o.setKunde(dbKD.getKunde(results.getInt("kundeId_fk")));
			if (getDetails)
			{
				DBDelOrdre dbDELO = new DBDelOrdre();
				o.setDelOrdre(dbDELO.getAllDelOrdre(o.getOrdreId()));
			}
		}
		catch (Exception e)
		{
			System.out.println("error in building the Ordre object");
		}
		return o;
	}

	private Ordre buildMultipleOrdrers(ResultSet results, boolean getDetails)
	{
		Ordre o = new Ordre();
		try
		{
			o.setOrdreId(results.getInt("ordreId"));
			o.setLeveringsDato(results.getLong("leveringsDato"));
			o.setOprettelsesDato(results.getLong("oprettelsesDato"));
			o.setNote(results.getString("note"));
			o.setOrdreType(results.getString("ordreType"));
			o.setDirectOnly(results.getBoolean("directOnly"));
			o.setTotal(results.getDouble("total"));
			o.setRabat(results.getDouble("rabat"));
			o.setDebit(results.getDouble("debit"));
			for (OrdreStatus s : OrdreStatus.values())
			{
				if (s.toString().equals(results.getString("ordreStatus")))
				{
					o.setOrdreStatus(s);
				}
			}
			o.setKunde(getKunde(results.getInt("kundeId_fk")));
			if (getDetails)
			{
				DBDelOrdre dbDELO = new DBDelOrdre();
				o.setDelOrdre(dbDELO.getAllDelOrdre(o.getOrdreId()));
			}
		}
		catch (Exception e)
		{
			System.out.println("error in building the Ordre object");
		}
		return o;
	}

	private Kunde getKunde(int kundeId)
	{
		Kunde tmpKunde = null;
		if (kdList == null)
		{
			kdList = new DBKunde().getAllKunde();
		}
		for (Kunde k : kdList)
		{
			if (k.getKundeId() == kundeId)
			{
				tmpKunde = k;
			}
		}
		return tmpKunde;
	}
}
